#This script runs all the setup sql in the proper order of execution

#import all modules needed
# from subprocess import call, PIPE, Popen
import os, re, sys, inspect, sqlalchemy
from {{ module.lower() }} import {{ module }}
from {{ module.lower() }}.utilities import new_session_from_engine, new_engine
from sqlalchemy.ext.declarative import declarative_base

# If user auth is required:
# get credentials (prompts for root password)
admin = {{ module }}.get_root_user()
pw = {{ module }}.get_password(admin)

Base = declarative_base()

## create session
# session = new_session({{ module }}.login(admin))
# session = new_session("sqlite:///:memory:")
engine = new_engine({{ module }}.login(admin))
session = new_session_from_engine(engine)

## creates all table models found in src/models
model_dir = os.path.normpath({{ module }}.get_project_root()+"/src/models")

for m in os.listdir(model_dir):
		if m.endswith(".py") and not m.startswith('config'):
			
			#import model as Python module
			module = __import__(m.split(".")[0])
			
			# get classes in model script
			for clz in inspect.getmembers(module, inspect.isclass):
				
				## gets the parent class
				clazz = inspect.getmro(clz[1])
				
				## can't be a base class
				if len(clazz) > 0:
					
					# is it a sqlalchemy class
					if clazz[1].__name__ == "Base":
						
						# creates the table from the class
						# session.execute(sqlalchemy.schema.CreateTable(clazz[0].__table__))
						clazz[0].__table__.create(engine, checkfirst=True)

def exec_sql_file(filename):
		
		#is it a sql file
		if filename.endswith(".sql"):
			
			print "Executing raw SQL...\t" + filename
			
			with open(filename, 'r') as fh:
				
				for sql in fh.read().split(';'):
					if len(sql) < 1:
						continue
	
					cmd = []
					for line in sql.split('\n'):
						if not line.strip().startswith("#"):
							cmd.append(line)
					
					try:
						session.execute("\n".join(cmd)+";")
					except:
						raise


#imports the sql dir into database
def exec_sql_dir(path):
	
	files = os.listdir(path)
	files.sort()
	
	for filename in files:
		exec_sql_file(path+"/"+filename)


#order of create
#create database statements are in this file
databases = {{ module }}.get_project_root()+'/src/setup/create_databases.sql'

#create table statements are in this directory
table_dir = {{ module }}.get_project_root()+'/src/setup/tables'

#create view statements are in this directory
view_dir = {{ module }}.get_project_root()+'/src/setup/views'

#create databases
exec_sql_file(databases)

#create tables using raw sql files
exec_sql_dir(table_dir)

#create views using raw sql files
exec_sql_dir(view_dir)

